package AccessDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Aluno;
import model.DisciplinaProfessor;
import model.Professor;

public class DisciplinaProfessorDAO {

	private Connection con;
	
	public DisciplinaProfessorDAO(){
		
		con = ConnectionDB.getInstance().getConnection();
	}
	
	public List<DisciplinaProfessor> recuperarTituloDasDisciplinasDoProfessor(String cpfProf) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("select idTurma, idMateria, idDisciplinaProf from " +
				"(DisciplinaProf inner join Disciplina on DisciplinaProf.idDisciplina = Disciplina.idDisciplina)" +
				"where cpfProf = ?");
		
		stmt.setString(1, cpfProf);
		stmt.execute();
		
		ResultSet rs = stmt.getResultSet();
		
		List<DisciplinaProfessor> disciplinasProf = new ArrayList<DisciplinaProfessor>();
		
		while(rs.next())
			disciplinasProf.add(gerarTituloDisciplinaProfessor(rs));
		
		return disciplinasProf;
	}
	
	public DisciplinaProfessor gerarTituloDisciplinaProfessor(ResultSet rs) throws SQLException
	{
		DisciplinaProfessor discProf = new DisciplinaProfessor();
		
		discProf.setIdDisciplinaProf(rs.getInt("idDisciplinaProf"));
		discProf.setIdTurma(rs.getInt("idTurma"));
		discProf.setIdMateria(rs.getInt("idMateria"));
		discProf.setTituloMateria(new MateriaDAO().retornaTituloMateria(discProf.getIdMateria()));
		discProf.setTituloTurma(new TurmaDAO().retornaTituloTurma(discProf.getIdTurma()));
				
		return discProf;
	}
	
	public DisciplinaProfessor gerarDisciplinaProfessor(int idDisciplinaProf, int idTurma) throws SQLException
	{
		DisciplinaProfessor discProf = new DisciplinaProfessor();
		
		discProf.setIdTurma(idTurma);
		discProf.setIdDisciplinaProf(idDisciplinaProf);
		discProf.setAlunos(new AlunoDAO().retornaAlunosTurma(idTurma));
		discProf.setAvaliacoes(new AvaliacaoDAO().retornaAvaliacoesDoProfessorNaTurma(idDisciplinaProf));
		discProf.setMateriais(new MaterialEstudoDAO().retornaMateriaisEstudoDisciplinaProf(idDisciplinaProf));
		discProf.setAno(new TurmaDAO().retornaAnoTurma(idTurma));
		
		return discProf;
	}
	
	public List<Professor> retornaProfessoresDaDisciplina(int idDisciplina) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("select Professor.cpfProf, nomeCompleto, nomeUsual, " +
				"email, telResidencial, celular, foto from DisciplinaProf inner join " +
				"Professor on DisciplinaProf.cpfProf = Professor.cpfProf where idDisciplina = ?");
		
		stmt.setInt(1, idDisciplina);
		stmt.execute();
		
		ResultSet rs = stmt.getResultSet();
		
		List<Professor> professores = new ArrayList<Professor>();		
		ProfessorDAO profDAO = new ProfessorDAO();
		
		while(rs.next()){
			professores.add(profDAO.gerarProfessor(rs));
		}
		
		return professores;
	}
	
	public int retornaIdDisciplinaProf(int idDisciplina, String cpfProf) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("select idDisciplinaProf from DisciplinaProf" +
				" where cpfProf = ? and idDisciplina = ?");
		
		stmt.setString(1, cpfProf);
		stmt.setInt(2, idDisciplina);
		stmt.execute();
		
		ResultSet rs = stmt.getResultSet();
		
		int idDisciplinaProf = 0;
		
		while(rs.next())
			idDisciplinaProf = rs.getInt("idDisciplinaProf");
		
		return idDisciplinaProf;
	}
	
	public void adicionarProfessorNaDisciplina(int idDisciplina, String cpfProf) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("insert into DisciplinaProf values(?,?)");
		stmt.setInt(1, idDisciplina);
		stmt.setString(2, cpfProf);
		
	    stmt.execute();

		
	}
	
	public void removerProfessorDaDisciplina(int idDisciplina, String cpfProf) throws SQLException
	{
		PreparedStatement stmt = con.prepareStatement("delete DisciplinaProf where idDisciplina = ? and cpfProf = ?");
		stmt.setInt(1, idDisciplina);
		stmt.setString(2, cpfProf);
		
		stmt.execute();
	}
	
}